﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Text;
using System.Threading.Tasks;
using Common;
using System.Data.SqlClient;
using System.Data;


namespace DAO
{
    public class DanhMucHangHoaDAO
    {
        /// <summary>
        /// Chen 1 ban ghi vao bang DanhMucHangHoa
        /// </summary>
        /// <param name="dmhh">object DanhMucHangHoa</param>
        public static void Insert(DanhMucHangHoa dmhh)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_InsertDanhMucHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iLoaiHangHoa_ID", SqlDbType.Int).Value = dmhh.iLoaiHangHoa_ID;
                sqlComm.Parameters.Add("@sMaHangHoa", SqlDbType.NVarChar).Value = dmhh.sMaHangHoa;
                sqlComm.Parameters.Add("@sTenHangHoa", SqlDbType.NVarChar).Value = dmhh.sTenHangHoa;
                sqlComm.Parameters.Add("@iDonViTinh_ID", SqlDbType.Int).Value = dmhh.iDonViTinh_ID;
                sqlComm.Parameters.Add("@dGiaBan", SqlDbType.Decimal).Value = dmhh.dGiaBan;
                sqlComm.Parameters.Add("@dGiaKhuyenMai", SqlDbType.Decimal).Value = dmhh.dGiaKhuyenMai;
                sqlComm.Parameters.Add("@sHinhDaiDien", SqlDbType.NVarChar).Value = dmhh.sHinhDaiDien;
                sqlComm.Parameters.Add("@sMoTa", SqlDbType.NVarChar).Value = dmhh.sMoTa;
                sqlComm.Parameters.Add("@bDacBiet", SqlDbType.Bit).Value = dmhh.bDacBiet;
                sqlComm.Parameters.Add("@bBanChay", SqlDbType.Bit).Value = dmhh.bBanChay;
                sqlComm.Parameters.Add("@bTrangChu", SqlDbType.Bit).Value = dmhh.bTrangChu;
                sqlComm.Parameters.Add("@bQuanLy", SqlDbType.Bit).Value = dmhh.bQuanLy;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Sua 1 ban ghi trong bang DanhMucHangHoa
        /// </summary>
        /// <param name="dmhh">object DanhMucHangHoa</param>
        public static void Update(DanhMucHangHoa dmhh)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_UpdateDanhMucHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iDanhMucHangHoa_ID", SqlDbType.Int).Value = dmhh.iDanhMucHangHoa_ID;
                sqlComm.Parameters.Add("@iLoaiHangHoa_ID", SqlDbType.Int).Value = dmhh.iLoaiHangHoa_ID;
                sqlComm.Parameters.Add("@sMaHangHoa", SqlDbType.NVarChar).Value = dmhh.sMaHangHoa;
                sqlComm.Parameters.Add("@sTenHangHoa", SqlDbType.NVarChar).Value = dmhh.sTenHangHoa;
                sqlComm.Parameters.Add("@iDonViTinh_ID", SqlDbType.Int).Value = dmhh.iDonViTinh_ID;
                sqlComm.Parameters.Add("@dGiaBan", SqlDbType.Decimal).Value = dmhh.dGiaBan;
                sqlComm.Parameters.Add("@dGiaKhuyenMai", SqlDbType.Decimal).Value = dmhh.dGiaKhuyenMai;
                sqlComm.Parameters.Add("@sHinhDaiDien", SqlDbType.NVarChar).Value = dmhh.sHinhDaiDien;
                sqlComm.Parameters.Add("@sMoTa", SqlDbType.NVarChar).Value = dmhh.sMoTa;
                sqlComm.Parameters.Add("@bDacBiet", SqlDbType.Bit).Value = dmhh.bDacBiet;
                sqlComm.Parameters.Add("@bBanChay", SqlDbType.Bit).Value = dmhh.bBanChay;
                sqlComm.Parameters.Add("@bTrangChu", SqlDbType.Bit).Value = dmhh.bTrangChu;
                sqlComm.Parameters.Add("@bQuanLy", SqlDbType.Bit).Value = dmhh.bQuanLy;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Xoa 1 ban ghi trong bang DanhMucHangHoa
        /// </summary>
        /// <param name="id">int id</param>
        public static void Delete(int id)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_DeleteDanhMucHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iDanhMucHangHoa_ID", SqlDbType.Int).Value = id;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lay tat ca ban ghi trong bang DanhMucHangHoa
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable SelectAll()
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectAllDanhMucHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(sqlComm);
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                sqlComm.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lay 1 ban ghi trong bang DanhMucHangHoa
        /// </summary>
        /// <param name="id">int id</param>
        /// <returns>object DanhMucHangHoa</returns>
        public static DanhMucHangHoa SelectById(int id)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectByIdDanhMucHangHoa", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iDanhMucHangHoa_ID", SqlDbType.Int).Value = id;
                DanhMucHangHoa dmhh = new DanhMucHangHoa(sqlComm.ExecuteReader());
                sqlComm.Dispose();
                return dmhh;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lấy các bản ghi của bảng DanhMucHangHoa theo chỉ số Trang
        /// </summary>
        /// <param name="pageIndex">pageIndex</param>
        /// <param name="pageSize">pageSize</param>
        /// <param name="totalRows">totalRowsOfTable</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectAllForPaging(int pageIndex, int pageSize, ref int totalRows)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectAllDanhMucHangHoaForPaging", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
                sqlComm.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                sqlComm.Parameters.Add("@totalRows", SqlDbType.Int).Value = totalRows;
                sqlComm.Parameters["@totalRows"].Direction = ParameterDirection.Output;
                SqlDataAdapter da = new SqlDataAdapter(sqlComm);
                DataTable dt = new DataTable();
                da.Fill(dt);
                totalRows = Convert.ToInt32(sqlComm.Parameters["@totalRows"].Value);
                da.Dispose();
                sqlComm.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DeleteLinq(int id)
        {
            try
            {
                DataContext dc = new DataContext(ConnectDB.GetConnect());
                Table<DanhMucHangHoa> dt = dc.GetTable<DanhMucHangHoa>();
                dt.DeleteOnSubmit(dt.SingleOrDefault(q => q.iDanhMucHangHoa_ID == id));
                dc.SubmitChanges();
                dc.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void UpdateLinq(DanhMucHangHoa dmhh)
        {
            try
            {
                DataContext dc = new DataContext(ConnectDB.GetConnect());
                Table<DanhMucHangHoa> dt = dc.GetTable<DanhMucHangHoa>();
                DanhMucHangHoa temp = dt.SingleOrDefault(q => q.iDanhMucHangHoa_ID == dmhh.iDanhMucHangHoa_ID);
                temp.bBanChay = dmhh.bBanChay;
                temp.bDacBiet = dmhh.bDacBiet;
                temp.bQuanLy = dmhh.bQuanLy;
                temp.bTrangChu = dmhh.bTrangChu;
                temp.dGiaBan = dmhh.dGiaBan;
                temp.dGiaKhuyenMai = dmhh.dGiaKhuyenMai;
                temp.iDonViTinh_ID = dmhh.iDonViTinh_ID;
                temp.iLoaiHangHoa_ID = dmhh.iLoaiHangHoa_ID;
                temp.sHinhDaiDien = dmhh.sHinhDaiDien;
                temp.sMaHangHoa = dmhh.sMaHangHoa;
                temp.sMoTa = dmhh.sMoTa;
                temp.sTenHangHoa = dmhh.sTenHangHoa;
                dc.SubmitChanges();
                dc.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void InsertLinq(DanhMucHangHoa dmhh)
        {
            try
            {
                DataContext dc = new DataContext(ConnectDB.GetConnect());
                Table<DanhMucHangHoa> dt = dc.GetTable<DanhMucHangHoa>();
                dt.InsertOnSubmit(dmhh);
                dc.SubmitChanges();
                dc.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DanhMucHangHoa SelectByIdLinq(int id)
        {
            try
            {
                DataContext dc = new DataContext(ConnectDB.GetConnect());
                Table<DanhMucHangHoa> dt = dc.GetTable<DanhMucHangHoa>();
                return dt.SingleOrDefault(q => q.iDanhMucHangHoa_ID == id);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static object SelectAllLinq()
        {
            try
            {
                DataContext dc = new DataContext(ConnectDB.GetConnect());
                Table<DanhMucHangHoa> dt = dc.GetTable<DanhMucHangHoa>();
                return dt.Select(e => e);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static object SelectAllLinqForPaging(int pageIndex, int pageSize, ref int totalRows)
        {
            try
            {
                DataContext dc = new DataContext(ConnectDB.GetConnect());
                Table<DanhMucHangHoa> dt = dc.GetTable<DanhMucHangHoa>();
                Table<LoaiHangHoa> lhh = dc.GetTable<LoaiHangHoa>();
                Table<DonViTinh> dvt = dc.GetTable<DonViTinh>();
                totalRows = dt.Count();
                pageIndex = (pageIndex - 1) * pageSize;
                return dt.AsEnumerable().Join(dvt, d => d.iDonViTinh_ID, v => v.iDonViTinh_ID, (d, v) => new { d, v })
                                        .Join(lhh, dd => dd.d.iLoaiHangHoa_ID, l => l.iLoaiHangHoa_ID, (dd, l) => new { dd, l })
                                        .Where((q, i) => i >= pageIndex && i < (pageIndex + pageSize))
                                        .Select((q, i) => new
                {
                    q.dd.d.bBanChay,
                    q.dd.d.bDacBiet,
                    q.dd.d.bQuanLy,
                    q.dd.d.bTrangChu,
                    q.dd.d.dGiaBan,
                    q.dd.d.iDanhMucHangHoa_ID,
                    imgPopOver = "../Images/" + q.dd.d.sHinhDaiDien,
                    q.dd.d.sHinhDaiDien,
                    q.dd.d.sMaHangHoa,
                    q.dd.d.sTenHangHoa,
                    q.l.sTenLoai,
                    q.dd.v.sTenDonViTinh,
                    STT = i + pageIndex + 1
                });
            }
            catch (Exception ex)
            {
                throw ex;

            }
        }

        public static object SelectByLoaiSanPhamLinq(int id, int pageIndex, int pageSize, ref int totalRows)
        {
            try
            {
                DataContext dc = new DataContext(ConnectDB.GetConnect());
                Table<DanhMucHangHoa> dt = dc.GetTable<DanhMucHangHoa>();
                totalRows = dt.Count();
                pageIndex = (pageIndex - 1) * pageSize;
                return dt.AsEnumerable().OrderByDescending(q => q.iDanhMucHangHoa_ID)
                    .Where((q, i) => q.iLoaiHangHoa_ID == id && i >= pageIndex && i < (pageIndex + pageSize))
                    .Select(q => q);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
                
        public static object SelectAllLinqTest()
        {
            try
            {
                SqlConnection conn = ConnectDB.GetConnect();
                DataContext db = new DataContext(conn);
                Table<LoaiHangHoa> dt = db.GetTable<LoaiHangHoa>();

                var q = dt.AsEnumerable().Select((currRow, index) => new { Row = currRow.sTenLoai, Index = index + 1 }).Where((e, i) => i > 3 && i < 10);
                return q;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
